/**
 * Copyright (c) 2019 Coder League
 * All rights reserved.
 *
 * File：OrdersDaoImpl.java
 * History:
 *         2019年6月6日: Initially created, CJH.
 */
package club.coderleague.ilsp.dao.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;
import club.coderleague.ilsp.common.domain.beans.GoodsInfo;
import club.coderleague.ilsp.common.domain.beans.OrderStatisticsExtension;
import club.coderleague.ilsp.common.domain.beans.PayOrder;
import club.coderleague.ilsp.common.domain.beans.RefundOrder;
import club.coderleague.ilsp.common.domain.beans.mobile.MobileOrderDetails;
import club.coderleague.ilsp.common.domain.beans.mobile.MobileOrders;
import club.coderleague.ilsp.common.domain.beans.mobile.MoblieOrderMerchantExtension;
import club.coderleague.ilsp.common.domain.enums.EntityState;
import club.coderleague.ilsp.common.domain.enums.IntegralChangedReason;
import club.coderleague.ilsp.common.domain.enums.IntegralRecordType;
import club.coderleague.ilsp.common.domain.enums.OrderDeliveryMode;
import club.coderleague.ilsp.common.domain.enums.OrderDeliveryState;
import club.coderleague.ilsp.common.domain.enums.OrderDeliveryTime;
import club.coderleague.ilsp.common.domain.enums.OrderPaymentMode;
import club.coderleague.ilsp.common.domain.enums.OrderPaymentState;
import club.coderleague.ilsp.common.domain.enums.OrderType;
import club.coderleague.ilsp.common.util.CommonUtil;
import club.coderleague.ilsp.dao.extension.OrdersDaoExtension;
import club.coderleague.ilsp.entities.Orders;

/**
 * 订单DaoImpl
 * 
 * @author CJH
 */
public class OrdersDaoImpl extends AbstractDataRepositoryExtension<Orders, Long, SnowflakeV4> implements OrdersDaoExtension {

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getgetBigCustomersMgrDao(java.util.Map)
	 */
	@Override
	public Page<OrderStatisticsExtension> getOrderStatisticsMgrDao(Map<String, Object> params) {
		Map<String, Object> param = new HashMap<String, Object>(); 
		String qsql = "select o.entityid as entityid, o.ordertotal as ordertotal, o.goodtotal as goodtotal, o.deliverycost as deliverycost, o.paymenttotal as paymenttotal, o.ordertime as ordertime, o.confirmtime as confirmtime, o.sendtime as sendtime, o.receivetime as receivetime, o.completetime as completetime, o.customerclose as customerclose, o.customerclosetime as customerclosetime, ms.marketname as marketname," + 
			"bcp.customerid as customerid, bcp.customername as customername, bcp.buyer as buyer, bcp.buyername as buyername, m.entityid as mid, m.merchantname as merchantname, o.entitystate, o.orderorigin as orderorigin, o.ordertype as ordertype, o.deliverystate as deliverystate, o.deliverymode as deliverymode, o.paymentstate as paymentstate, o.deliverytime as deliverytime, o.deliverystarttime as deliverystarttime, o.deliveryendtime as deliveryendtime, " +
			"(select count(ro.entityid) from Refundrecords ro where ro.orderid = o.entityid and ro.entitystate = " + EntityState.OBLIGATION.getValue() + ") as refundstate, o.cancelreason as cancelreason, o.memberid as memberid, (select memberphone from Members where entityid = o.memberid) as memberphone " +
			"from Orders o left join (select bcp.entityid as buyer, bcp.personname as buyername, big.entityid as customerid, big.customername as customername from Bigcustomerpersons bcp,Bigcustomers big where bcp.customerid = big.entityid) as bcp on o.purchaserid = bcp.buyer left join Merchants m on o.merchantid = m.entityid left join Markets ms on ms.entityid = o.marketid where o.entitystate in (:istate) ";
		String csql = "select count(o.entityid) from Orders o left join (select bcp.entityid as buyer, bcp.personname as buyername, big.entityid as customerid, big.customername as customername from Bigcustomerpersons bcp,Bigcustomers big where bcp.customerid = big.entityid) as bcp on o.purchaserid = bcp.buyer left join Merchants m on o.merchantid = m.entityid left join Markets ms on ms.entityid = o.marketid where o.entitystate in (:istate) ";
		
		// 订单状态。
		if (params.get("entitystate") != null && StringUtils.isNotBlank(params.get("entitystate").toString())) { 
			List<Integer> entitystates = new ArrayList<Integer>();
			for (String id : (params.get("entitystate").toString()).split(",")) {
				entitystates.add(Integer.parseInt(id));
			}
            param.put("istate", entitystates);
        } else param.put("istate", Arrays.asList(new Integer[]{EntityState.TBD.getValue(), EntityState.CONFIRMED.getValue(), EntityState.OTS.getValue(), EntityState.HBS.getValue(), EntityState.CANC.getValue(), EntityState.DELETED.getValue()}));
		
		// 关键字。
		if (params.get("keyword") != null && StringUtils.isNotBlank(params.get("keyword").toString())) { 
			qsql += "and (o.entityid like :keyword or o.cancelreason like :keyword or ms.marketname like :keyword) ";
			csql += "and (o.entityid like :keyword or o.cancelreason like :keyword or ms.marketname like :keyword) ";
            param.put("keyword", "%" + (String) params.get("keyword") + "%");
        }
		
		// 商户名称。
		if (params.get("merchantid") != null && StringUtils.isNotBlank(params.get("merchantid").toString())) { 
			List<Long> mids = new ArrayList<Long>();
			for (String id : (params.get("merchantid").toString()).split(",")) {
				mids.add(Long.valueOf(id));
			}
			qsql += "and o.merchantid in (:mids) ";
			csql += "and o.merchantid in (:mids) ";
            param.put("mids", mids);
        }
		
		// 大客户。
		if (params.get("customerid") != null && StringUtils.isNotBlank(params.get("customerid").toString())) { 
			List<Long> cids = new ArrayList<Long>();
			for (String id : (params.get("customerid").toString()).split(",")) {
				cids.add(Long.valueOf(id));
			}
			qsql += "and bcp.customerid in (:cids) ";
			csql += "and bcp.customerid in (:cids) ";
            param.put("cids", cids);
        }
		
		// 订单来源。
		if (params.get("orderorigin") != null && StringUtils.isNotBlank(params.get("orderorigin").toString())) { 
			List<Integer> orderorigins = new ArrayList<Integer>();
			for (String id : (params.get("orderorigin").toString()).split(",")) {
				orderorigins.add(Integer.parseInt(id));
			}
			qsql += "and o.orderorigin in (:orderorigins) ";
			csql += "and o.orderorigin in (:orderorigins) ";
            param.put("orderorigins", orderorigins);
        }
		
		// 订单类型。
		if (params.get("ordertype") != null && StringUtils.isNotBlank(params.get("ordertype").toString())) { 
			List<Integer> ordertypes = new ArrayList<Integer>();
			for (String id : (params.get("ordertype").toString()).split(",")) {
				ordertypes.add(Integer.parseInt(id));
			}
			qsql += "and o.ordertype in (:ordertypes) ";
			csql += "and o.ordertype in (:ordertypes) ";
            param.put("ordertypes", ordertypes);
        }
		
		// 配送状态。
		if (params.get("deliverystate") != null && StringUtils.isNotBlank(params.get("deliverystate").toString())) { 
			List<Integer> deliverystates = new ArrayList<Integer>();
			for (String id : (params.get("deliverystate").toString()).split(",")) {
				deliverystates.add(Integer.parseInt(id));
			}
			qsql += "and o.deliverystate in (:deliverystates) ";
			csql += "and o.deliverystate in (:deliverystates) ";
            param.put("deliverystates", deliverystates);
        }
		
		// 配送方式。
		if (params.get("deliverymode") != null && StringUtils.isNotBlank(params.get("deliverymode").toString())) { 
			List<Integer> deliverymodes = new ArrayList<Integer>();
			for (String id : (params.get("deliverymode").toString()).split(",")) {
				deliverymodes.add(Integer.parseInt(id));
			}
			qsql += "and o.deliverymode in (:deliverymodes) ";
			csql += "and o.deliverymode in (:deliverymodes) ";
            param.put("deliverymodes", deliverymodes);
        }
		
		// 支付状态。
		if (params.get("paymentstate") != null && StringUtils.isNotBlank(params.get("paymentstate").toString())) { 
			List<Integer> paymentstates = new ArrayList<Integer>();
			for (String id : (params.get("paymentstate").toString()).split(",")) {
				paymentstates.add(Integer.parseInt(id));
			}
			qsql += "and o.paymentstate in (:paymentstates) ";
			csql += "and o.paymentstate in (:paymentstates) ";
            param.put("paymentstates", paymentstates);
        }
		
		qsql += "order by o.entityid desc, m.entityid desc, bcp.customerid desc ";
		return super.queryCustomBeanPageBySql(OrderStatisticsExtension.class, csql, qsql, Integer.parseInt(params.get("pageIndex").toString()), Integer.parseInt(params.get("pageSize").toString()), param);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getRefundRecordByOrderidDao(java.lang.Long)
	 */
	@Override
	public Map<String, Object> getRefundRecordByOrderidDao(Long orderid) {
		String sql = "select entityid, applytime, applydesc, partialrefund, refundtotal, integralrefund, diamondrefund from Refundrecords where orderid = ?0 and entitystate = ?1 order by applytime desc limit 1";
		return super.queryMapBySql(sql, orderid, EntityState.OBLIGATION.getValue());
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getRefundGoodByOrderidDao(Long, String)
	 */
	@Override
	public List<Map<String, Object>> getRefundGoodByOrderidDao(Long orderid, String recordid) {
		String sql = "select og.entityid, concat(og.goodname, '【', og.goodspec, '】') as goodname, og.goodprice, og.goodnumber, og.goodtotal, og.paymenttotal, og.integralpay, og.diamondpay from Ordergoods og, Refundgoods rg where og.entityid = rg.goodid and og.orderid = ?0 and rg.entitystate = ?1 and rg.recordid = ?2 ";
		return super.queryMapListBySql(sql, orderid, EntityState.VALID.getValue(), Long.valueOf(recordid));
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getOrderGoodsByOrderIdDao(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> getOrderGoodsByOrderIdDao(Map<String, Object> params) {
		String sql = "select entityid, entitystate as refunded, goodname, goodorigin, goodpicture, goodspec, goodprice, goodnumber, goodtotal from Ordergoods where orderid = ?0 order by createtime asc";
		return super.queryMapListBySql(sql, params.get("orderid").toString());
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#queryPayOrder(long)
	 */
	@Override
	public PayOrder queryPayOrder(long order) {
		String sql = "select entityid as orderid, ordertype, ordertotal, entitystate as orderstate, paymenttotal, paymentstate, paymentmode, paymenttradeno, tradecreatetime, diamondpay from orders where entityid = ?0";
		return super.queryCustomBeanBySql(PayOrder.class, sql, order);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#savePayTrade(long, club.coderleague.ilsp.common.domain.enums.OrderPaymentMode, java.lang.String)
	 */
	@Override
	public void savePayTrade(long order, OrderPaymentMode mode, String trade) {
		String jpql = "update Orders set paymentmode = ?0, paymenttradeno = ?1, tradecreatetime = ?2 where entityid = ?3";
		super.executeUpdate(jpql, mode.getValue(), trade, new Date(), order);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#queryRefundOrder(long)
	 */
	@Override
	public RefundOrder queryRefundOrder(long order) {
		String sql = "select o.entityid as orderid, o.paymenttotal, rr.refundtotal, o.entitystate as orderstate, o.ordertype, o.paymentstate, o.paymentmode, o.refundendtime, rr.entityid as refundid, rr.partialrefund " 
			+ "from orders o, refundrecords rr where rr.orderid = o.entityid and rr.entitystate = ?0 and o.entityid = ?1";
		return super.queryCustomBeanBySql(RefundOrder.class, sql, EntityState.REFUNDING.getValue(), order);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#queryPartialOrder(long, boolean)
	 */
	@Override
	public Orders queryPartialOrder(long order, boolean refund) {
		if (refund) {
			String sql = "select o.entityid, o.paymentmode, o.paymentstate, " 
				+ "(case when o.paymentstate = ?0 then o.refundtradeno else (case when rr.partialrefund = ?1 then cast(rr.entityid as char) else cast(o.entityid as char) end) end) as refundtradeno, " 
				+ "o.refundtime from orders o, refundrecords rr where o.entityid = rr.orderid and o.entityid = ?2";
			return super.queryCustomBeanBySql(Orders.class, sql, OrderPaymentState.REFUNDED.getValue(), true, order);
		} else {
			String sql = "select entityid, paymentmode, paymentstate, paymenttradeno, paytime from orders where entityid = ?0";
			return super.queryCustomBeanBySql(Orders.class, sql, order);
		}
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#findGoodsinfoBySaleid(java.lang.Long)
	 */
	@Override
	public GoodsInfo findGoodsinfoBySaleid(Long saleid) {
		String sql = "select ms.merchantid as merchantid, ms.marketid as marketid, g.goodname as goodname, g.goodorigin as goodorigin,"
				+ " gp.photopath as goodpicture, gsa.specdefine as goodspec from goodsales gs"
				+ " left join goodspecs gsa on gsa.entityid = gs.specid"
				+ " left join goods g on g.entityid = gsa.goodid"
				+ " left join goodphotos gp on gp.goodid = g.entityid and gp.entitystate = ?1 and gp.coverphoto = ?2"
				+ " left join merchantsettles ms on ms.entityid = gs.settleid"
				+ " where gs.entityid = ?0";
		return super.queryCustomBeanBySql(GoodsInfo.class, sql, saleid, EntityState.VALID.getValue(), true);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#findOrdersInfo(java.lang.Long)
	 */
	@Override
	public Map<String, Object> findOrdersInfo(Long ordersid) {
		String sql = "select o.entitystate as ordersstate, o.ordertotal as ordertotal, o.goodtotal as goodtotal, o.paymenttotal as paymenttotal,"
				+ " o.paymenttradeno as paymenttradeno, o.tradecreatetime as tradecreatetime, o.tradecompletetime as tradecompletetime,"
				+ " o.paymentstate as paymentstate, o.paymentmode as paymentmode, o.integralpay as integralpay, o.diamondpay as diamondpay,"
				+ " o.ordertime as ordertime, o.confirmtime as confirmtime, o.paytime as paytime, o.completetime as completetime,"
				+ " o.canceltype as canceltype, o.canceltime as canceltime, o.cancelreason as cancelreason, m.entityid as memberid,"
				+ " m.memberphone as memberphone, ir.changedintegral as incomeintegral"
				+ " from orders o"
				+ " left join members m on m.entityid = o.memberid"
				+ " left join integralrecords ir on ir.orderid = o.entityid and ir.recordtype = ?1 and ir.changedreason = ?2"
				+ " where o.entityid = ?0";
		return super.queryMapBySql(sql, ordersid, IntegralRecordType.INCOME.getValue(), IntegralChangedReason.ORDER_REVENUE.getValue());
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getMobileOrderByMemberIdDao(Long, String)
	 */
	@Override
	public List<MoblieOrderMerchantExtension> getMobileOrderByMemberIdDao(Long memberid, String keyword) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select o.entityid as orderid, (case when o.refundendtime > now() then 'true' else 'false' end) as showapply, m.entityid as merchantid, m.merchantname as merchantname, m.merchantphone as merchantphone, m.entitystate as merchantstate, (select count(ref.orderid) from Refundrecords ref where ref.orderid = o.entityid and ref.entitystate in (:refstate)) as recordcount "
				+ "from Orders o, Merchants m where o.entitystate = :entitystate and o.deliverystate = :deliverystate and o.ordertype = :ordertype and o.merchantid = m.entityid and o.memberid = :memberid ";
		param.put("refstate", Arrays.asList(new Integer[] {EntityState.OBLIGATION.getValue(), EntityState.REFUNDING.getValue(), EntityState.REFUNDED.getValue()}));
		param.put("entitystate", EntityState.OTS.getValue());
		param.put("deliverystate", OrderDeliveryState.RECEIVED.getValue());
		param.put("ordertype", OrderType.NORMAL.getValue());
		param.put("memberid", memberid);
		if (keyword != null && !"".equals(keyword)) {
			sql += "and (o.entityid like :keyword or o.entityid in (select og.orderid from Goodsales gs, Ordergoods og where gs.entityid = og.saleid and (gs.goodnumber like :keyword or og.goodname like :keyword))) ";
			param.put("keyword", "%" + keyword + "%");
		}
		sql += "order by o.entityid desc";
		return super.queryCustomBeanListBySql(MoblieOrderMerchantExtension.class, sql, param);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getOrderMerchantByOrderidDao(java.lang.Long)
	 */
	@Override
	public MoblieOrderMerchantExtension getOrderMerchantByOrderidDao(Long orderid) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select o.entityid as orderid, (case when o.refundendtime > now() then 'true' else 'false' end) as showapply, m.entityid as merchantid, m.merchantname as merchantname, m.merchantphone as merchantphone, m.entitystate as merchantstate, o.entitystate as entitystate, o.deliverystate as deliverystate "
				+ "from Orders o, Merchants m where o.merchantid = m.entityid and o.entityid = :entityid";
		param.put("entityid", orderid);
		return super.queryCustomBeanBySql(MoblieOrderMerchantExtension.class, sql, param);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getRefundHandleByMemberIdDao(Long, List, String)
	 */
	@Override
	public List<MoblieOrderMerchantExtension> getRefundHandleByMemberIdDao(Long memberid, List<Integer> refundstate, String keyword) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select o.entityid as orderid, m.entityid as merchantid, m.merchantname as merchantname, m.merchantphone as merchantphone, m.entitystate as merchantstate, rr.entityid as refundrecordid, rr.refundtotal as refundtotal, rr.entitystate as refundstate "
				+ "from Orders o, Merchants m, Refundrecords rr where o.entityid = rr.orderid and rr.entitystate in (:istates) and o.merchantid = m.entityid and o.memberid = :memberid ";
		param.put("istates", refundstate);
		param.put("memberid", memberid);
		if (keyword != null && !"".equals(keyword)) {
			sql += "and (o.entityid like :keyword or o.entityid in (select og.orderid from Goodsales gs, Ordergoods og where gs.entityid = og.saleid and (gs.goodnumber like :keyword or og.goodname like :keyword))) ";
			param.put("keyword", "%" + keyword + "%");
		}
		sql += "order by o.entityid desc ";
		return super.queryCustomBeanListBySql(MoblieOrderMerchantExtension.class, sql, param);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getRefundHandleByOrderIdDao(java.lang.Long)
	 */
	@Override
	public MoblieOrderMerchantExtension getRefundHandleByOrderIdDao(Long orderid) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select o.entityid as orderid, m.entityid as merchantid, m.merchantname as merchantname, m.merchantphone as merchantphone, m.entitystate as merchantstate, rr.entityid as refundrecordid, rr.refundtotal as refundtotal, rr.applydesc as applydesc, rr.applytime as applytime "
				+ "from Orders o, Merchants m, Refundrecords rr where o.entityid = rr.orderid and o.merchantid = m.entityid and o.entityid = :entityid ";
		param.put("entityid", orderid);
		return super.queryCustomBeanBySql(MoblieOrderMerchantExtension.class, sql, param);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.OrdersDaoExtension#getRefundRecordByOrderIdDao(java.lang.Long)
	 */
	@Override
	public MoblieOrderMerchantExtension getRefundRecordByOrderIdDao(Long orderid) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select o.entityid as orderid, m.entityid as merchantid, m.merchantname as merchantname, m.merchantphone as merchantphone, m.entitystate as merchantstate, rr.entityid as refundrecordid, rr.refundtotal as refundtotal, rr.applydesc as applydesc, rr.applytime as applytime, rr.refundtime as refundtime, rr.tradeno as tradeno, rr.entitystate as refundstate, rr.handledesc as refunddesc "
				+ "from Orders o, Merchants m, Refundrecords rr where o.entityid = rr.orderid and o.merchantid = m.entityid and o.entityid = :entityid ";
		param.put("entityid", orderid);
		return super.queryCustomBeanBySql(MoblieOrderMerchantExtension.class, sql, param);
	}

	@Override
	public Long getTheMemberWaitPayOrders(Long memberid) {
		String sql = " select count(entityid) from Orders where MemberId = ?0 and EntityState = ?1 and PaymentState = ?2 and OrderType IN (?3) ";
		return super.queryLongBySql(sql, memberid, EntityState.CONFIRMED.getValue(), OrderPaymentState.UNPAID.getValue(), Arrays.asList(new Integer[] {OrderType.NORMAL.getValue(),OrderType.ACTIVITY.getValue()}));
	}
	
	@Override
	public Long getTheMemberWaitTakeOrders(Long memberid) {
		String sql = " select count(entityid) from Orders where MemberId = ?0 and EntityState = ?1 and PaymentState = ?2 and DeliveryState = ?3 and OrderType IN (?4) ";
		return super.queryLongBySql(sql, memberid, EntityState.CONFIRMED.getValue(), OrderPaymentState.PAID.getValue(), OrderDeliveryState.SHIPPED.getValue(), Arrays.asList(new Integer[] {OrderType.NORMAL.getValue(),OrderType.ACTIVITY.getValue()}));
	}

	@Override
	public List<MobileOrders> getTheOrdersByMemberidAndTabIndex(Long memberid, Integer entityidState, Integer deliveryState, Integer paymentState) {
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" select concat(o.entityid) as orderid, m.entityid as merchantid, m.MerchantName as merchantname, (select SUM(og.PaymentTotal) from OrderGoods og where og.OrderId = o.entityid) as ordermoney,(select COUNT(og.entityid) from OrderGoods og where og.OrderId = o.entityid) as ordernum "
				+ " , ("
				+ " case when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.UNPAID.getValue()+" then '待付款' "
				+ " when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.PAID.getValue()+" and o.DeliveryState = "+OrderDeliveryState.UNSHIPPED.getValue()+" then '待发货' "
				+ " when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.PAID.getValue()+" and o.DeliveryState = "+OrderDeliveryState.SHIPPED.getValue()+" then '待收货' "
				+ " when o.EntityState = "+EntityState.OTS.getValue()+" and o.DeliveryState = "+OrderDeliveryState.RECEIVED.getValue()+" then '已完成' "
				+ " when o.EntityState = "+EntityState.CANC.getValue()+" then '已取消' "
				+ " else '' end) as orderstatestr "
				+ " , ("
				+ " case when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.UNPAID.getValue()+" then '2' "
				+ " when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.PAID.getValue()+" and o.DeliveryState = "+OrderDeliveryState.UNSHIPPED.getValue()+" then '3' "
				+ " when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.PAID.getValue()+" and o.DeliveryState = "+OrderDeliveryState.SHIPPED.getValue()+" then '4' "
				+ " when o.EntityState = "+EntityState.OTS.getValue()+" and o.DeliveryState = "+OrderDeliveryState.RECEIVED.getValue()+" then '5' "
				+ " when o.EntityState = "+EntityState.CANC.getValue()+" then '6' "
				+ " else '' end) as orderstate "
				+ " , ("
				+ " case when o.PaymentMode = "+OrderPaymentMode.WECHAT.getValue()+" then '"+OrderPaymentMode.WECHAT.getKey()+"' "
				+ " when o.PaymentMode = "+OrderPaymentMode.ALIPAY.getValue()+" then '"+OrderPaymentMode.ALIPAY.getKey()+"' "
				+ " when o.PaymentMode = "+OrderPaymentMode.UNIONPAY.getValue()+" then '"+OrderPaymentMode.UNIONPAY.getKey()+"' "
				+ " else '' end) as paymodel "
				+ " ,o.OrderOrigin as orderorigin "
				+ " from Orders o, Merchants m where o.MerchantId = m.entityid and o.MemberId = :memberid ");
		
		map.put("memberid", memberid);
		if(CommonUtil.isEmpty(entityidState) && CommonUtil.isEmpty(deliveryState) && CommonUtil.isEmpty(paymentState)) {
			sql.append(" and ((o.EntityState = :confirmedState and o.PaymentState = :unpaidState) or (o.EntityState = :confirmedState and o.PaymentState = :paidState and o.DeliveryState = :unshippedState) or (o.EntityState = :confirmedState and o.PaymentState = :paidState and o.DeliveryState = :shippedState) or (o.EntityState = :otsState and o.DeliveryState = :receivedState) or (o.EntityState = :cancState) ) ");
			map.put("confirmedState", EntityState.CONFIRMED.getValue());
			map.put("unpaidState", OrderPaymentState.UNPAID.getValue());
			map.put("paidState", OrderPaymentState.PAID.getValue());
			map.put("unshippedState", OrderDeliveryState.UNSHIPPED.getValue());
			map.put("shippedState", OrderDeliveryState.SHIPPED.getValue());
			map.put("otsState", EntityState.OTS.getValue());
			map.put("receivedState", OrderDeliveryState.RECEIVED.getValue());
			map.put("cancState", EntityState.CANC.getValue());
		}else {
			if(!CommonUtil.isEmpty(entityidState)) {
				sql.append(" and o.EntityState = :entityidState ");
				map.put("entityidState", entityidState);
			}
			if(!CommonUtil.isEmpty(deliveryState)) {
				sql.append(" and o.DeliveryState = :deliveryState ");
				map.put("deliveryState", deliveryState);
			}
			if(!CommonUtil.isEmpty(paymentState)) {
				sql.append(" and o.PaymentState = :paymentState ");
				map.put("paymentState", paymentState);
			}
		}
		sql.append(" and o.OrderType IN (:ordertype) order by o.CreateTime desc ");
		map.put("ordertype", Arrays.asList(new Integer[] {OrderType.NORMAL.getValue(),OrderType.ACTIVITY.getValue()}));
		return super.queryCustomBeanListBySql(MobileOrders.class, sql.toString(), map);
	}

	@Override
	public MobileOrderDetails getTheMobileOrderDetailsByOrderidAndTabIndex(Long orderid, Integer orderstate) {
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" select concat(o.entityid) as orderid "
				+ " , ("
				+ " case when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.UNPAID.getValue()+" then '2' "
				+ " when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.PAID.getValue()+" and o.DeliveryState = "+OrderDeliveryState.UNSHIPPED.getValue()+" then '3' "
				+ " when o.EntityState = "+EntityState.CONFIRMED.getValue()+" and o.PaymentState = "+OrderPaymentState.PAID.getValue()+" and o.DeliveryState = "+OrderDeliveryState.SHIPPED.getValue()+" then '4' "
				+ " when o.EntityState = "+EntityState.OTS.getValue()+" and o.DeliveryState = "+OrderDeliveryState.RECEIVED.getValue()+" then '5' "
				+ " when o.EntityState = "+EntityState.CANC.getValue()+" then '6' "
				+ " else '' end) as orderstate "
				+ " ,o.CancelReason as canclereason ");
		if(orderstate == 2) {
			//订单中（确认时间） + 系统配置（支付超时）
			sql.append(" ,DATE_FORMAT(timediff(date_add(o.ConfirmTime, interval (select sc.PayTimeout from SystemConfigs sc where sc.EntityState = "+EntityState.VALID.getValue()+") minute),NOW()),'%k小时%i分钟') as surplustime ");
			sql.append(" ,("
					+ "case when o.PaymentMode = "+OrderPaymentMode.WECHAT.getValue()+" then '"+OrderPaymentMode.WECHAT.getKey()+"' "
					+ " when o.PaymentMode = "+OrderPaymentMode.ALIPAY.getValue()+" then '"+OrderPaymentMode.ALIPAY.getKey()+"' "
					+ " when o.PaymentMode = "+OrderPaymentMode.UNIONPAY.getValue()+" then '"+OrderPaymentMode.UNIONPAY.getKey()+"' "
					+ " else '' end) as paymodel ");
		}
		sql.append(" ,o.Receiver as receivername, o.MobilePhone as receivephone "
				 + " ,concat((select a.AreaName from Areas a where a.entityid = o.Province),(select a.AreaName from Areas a where a.entityid = o.City),(select a.AreaName from Areas a where a.entityid = o.County),(select a.AreaName from Areas a where a.entityid = o.Street)) as address "
				 + " ,o.DetailAddress as detailaddress, m.entityid merchantid, m.MerchantName as merchantname, concat(o.EntityId) as ordercode, DATE_FORMAT(o.OrderTime, '%Y%-%m-%d %H:%i:%s') as ordertime "
				 + " ,(case when o.PaymentMode = "+OrderPaymentMode.WECHAT.getValue()+" then '"+OrderPaymentMode.WECHAT.getText()+"' when o.PaymentMode = "+OrderPaymentMode.ALIPAY.getValue()+" then '"+OrderPaymentMode.ALIPAY.getText()+"' when o.PaymentMode = "+OrderPaymentMode.UNIONPAY.getValue()+" then '"+OrderPaymentMode.UNIONPAY.getText()+"' else '' end ) as paymentmode "
		 		 + " ,(case when o.DeliveryMode = "+OrderDeliveryMode.NO_DELIVERY.getValue()+" then '"+OrderDeliveryMode.NO_DELIVERY.getText()+"' when o.DeliveryMode = "+OrderDeliveryMode.MARKET_DELIVERY.getValue()+" then '"+OrderDeliveryMode.MARKET_DELIVERY.getText()+"' else '' end ) as deliverymode "
 		 		 + " ,(case when o.deliverytime = "+OrderDeliveryTime.CUSTOMIZED.getValue()+" then (concat(DATE_FORMAT(o.DeliveryStartTime, '%Y%-%m-%d %H:%i:%s'),'-',DATE_FORMAT(o.DeliveryEndTime, '%Y%-%m-%d %H:%i:%s'))) else '正常配送' end ) as deliverytime "
 		 		 + " ,o.goodtotal as goodtotal, o.deliverycost as deliverycost, o.ordertotal as ordertotal, o.diamondpay as diamondpay, o.paymenttotal as paymenttotal "
 		 		 + " ,o.OrderOrigin as orderorigin ");
		
		sql.append(" from Orders o, Merchants m where o.MerchantId = m.entityid and o.entityid = :orderid and o.OrderType IN (:ordertype) ");
		map.put("orderid", orderid);
		map.put("ordertype", Arrays.asList(new Integer[] {OrderType.NORMAL.getValue(),OrderType.ACTIVITY.getValue()}));
		return super.queryCustomBeanBySql(MobileOrderDetails.class, sql.toString(), map);
	}

	@Override
	public Long getTheOrderIsNoRefundType(String orderid) {
		String sql = " select "
				+ " case when (((select rr.EntityState from RefundRecords rr where rr.OrderId = o.entityid order by rr.ApplyTime desc limit 1) = "+EntityState.OBLIGATION.getValue()+") or ((select rr.EntityState from RefundRecords rr where rr.OrderId = o.entityid order by rr.ApplyTime desc limit 1) = "+EntityState.REFUNDING.getValue()+") or ((select rr.EntityState from RefundRecords rr where rr.OrderId = o.entityid order by rr.ApplyTime desc limit 1) = "+EntityState.REFUNDED.getValue()+")) then 3 "
				+ " when (((select count(og.entityid) from OrderGoods og where og.OrderId = o.entityid) > 0) && ((select count(og.entityid) from OrderGoods og where og.OrderId = o.entityid and og.SupportRefund = 1) = 0)) then 2 "
				+ " when TIMEDIFF(now(),o.RefundEndTime) > 0 then 1 "
				+ " else 0 end "
				+ " from Orders o where o.entityid = ?0 ";
		return super.queryLongBySql(sql, orderid);
	}
}
